USE [TEMP_CM]
GO
/****** Object:  StoredProcedure [dbo].[spGetReadings]    Script Date: 04/20/2013 14:57:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   procedure [dbo].[spGetReadings]
	@pSocket VARCHAR(50) = NULL,
	@pStartDatetime DATETIME, --YYYYMMDD HH:MI
	@pEndDatetime DATETIME, --YYYYMMDD HH:MI
	@pIdvar VARCHAR(60) = NULL,
	@pVartype VARCHAR(1), -- R registros, M load profile, V eventos
	
	-- Nuevo parametro obligatorio
	@pTop smallint, -- top param
	@pOffset smallint, -- offset param
	
	-- Nuevos parametros opcionales
	--SOCKET_ID, es el mismo pSocket
	@pIdClient VARCHAR(50) = NULL, 
	@pIdDevice VARCHAR(50) = NULL, 
	@pUOM VARCHAR(10) = NULL, 
	@pChannel smallint = NULL,
	--VAR_DESCRIPTION, es el mismo pIdVar
	@pInterval smallint = NULL,
	@pKE float = NULL, 
	--DATETIME, es le mismo @pStartDatetime y @pEndDatetime 
	@UsageData float = NULL,
	
	
	@Cerror VARCHAR(100) = '' output
AS
BEGIN
DECLARE
	@lvSql1 VARCHAR(50) = '';

	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	SET NOCOUNT ON
	BEGIN TRAN GetLPReadings  
	BEGIN TRY
		--PRINT left(convert( varchar, @pDatetime, 120),5)
		
		IF @pVartype = 'M'
		BEGIN			
			select top (@pTop) t1.* from (
				SELECT 
				ROW_NUMBER() OVER ( ORDER BY dbo.LOAD_PROFILE_READING.DATETIME, dbo.CONFIG.DESCRIPTION, dbo.READING_DETAIL.CHANNEL ) as rowid,
				dbo.DEVICE_SOCKET_DATES.IDSOCKET AS SOCKET_ID, 
				dbo.SOCKETS.IDCLIENT AS CLIENT_ID,
				dbo.READING_DETAIL.NOINS AS DEVICE_ID,
				dbo.READING_DETAIL.UOM AS UOM,
				dbo.READING_DETAIL.CHANNEL AS CHANNEL,
				dbo.CONFIG.DESCRIPTION AS VAR_DESCRIPTION,
				dbo.READING_DETAIL.NOINTERVALS AS INTERVAL,
				dbo.CONFIG.KE AS KE,
				dbo.LOAD_PROFILE_READING.DATETIME AS DATETIME,
				dbo.LOAD_PROFILE_READING.USAGE_DATA AS USAGE_DATA 
				FROM            dbo.DEVICE_SOCKET_DATES INNER JOIN
										 dbo.CONFIG ON dbo.DEVICE_SOCKET_DATES.NOINS = dbo.CONFIG.NOINS INNER JOIN
										 dbo.READING_DETAIL ON dbo.CONFIG.NOINS = dbo.READING_DETAIL.NOINS AND dbo.CONFIG.CHANNEL = dbo.READING_DETAIL.CHANNEL INNER JOIN
										 dbo.LOAD_PROFILE_READING ON dbo.LOAD_PROFILE_READING.ID_READING_DETAIL = dbo.READING_DETAIL.ID_READING_DETAIL  INNER JOIN
										 dbo.SOCKETS ON dbo.DEVICE_SOCKET_DATES.IDSOCKET = dbo.SOCKETS.IDSOCKET
				WHERE CONFIG.VARTYPE = 'M'
					AND LOAD_PROFILE_READING.DATETIME BETWEEN @pStartDatetime AND @pEndDatetime
					AND LOAD_PROFILE_READING.DATETIME >= DEVICE_SOCKET_DATES.START_DATE
					AND LOAD_PROFILE_READING.DATETIME <= ISNULL(DEVICE_SOCKET_DATES.END_DATE,GETDATE())
					AND dbo.DEVICE_SOCKET_DATES.IDSOCKET = ISNULL(@pSocket, dbo.DEVICE_SOCKET_DATES.IDSOCKET)
					AND dbo.CONFIG.DESCRIPTION = ISNULL(@pIdvar, dbo.CONFIG.DESCRIPTION)
					-- Nuevas condiciones para nuevos parametros opcionales
					AND dbo.SOCKETS.IDCLIENT = ISNULL(@pIdClient, dbo.SOCKETS.IDCLIENT)
					AND dbo.READING_DETAIL.NOINS = ISNULL(@pIdDevice, dbo.READING_DETAIL.NOINS)
					AND dbo.READING_DETAIL.UOM = ISNULL(@pUOM, dbo.READING_DETAIL.UOM)
					AND dbo.READING_DETAIL.CHANNEL = ISNULL(@pChannel, dbo.READING_DETAIL.CHANNEL)
					AND dbo.READING_DETAIL.NOINTERVALS = ISNULL(@pInterval, dbo.READING_DETAIL.NOINTERVALS)
					AND dbo.CONFIG.KE = ISNULL(@pKE, dbo.CONFIG.KE)
					AND dbo.LOAD_PROFILE_READING.USAGE_DATA = ISNULL(@UsageData, dbo.LOAD_PROFILE_READING.USAGE_DATA)		
				) as t1
			where t1.rowid between @pOffset and (@pOffset + @pTop)
			ORDER BY t1.DATETIME, t1.VAR_DESCRIPTION, t1.CHANNEL			
		END
		ELSE
		IF @pVartype = 'R'
		BEGIN
			select top (@pTop) t1.* from (
				SELECT
				ROW_NUMBER() OVER ( ORDER BY dbo.READREGS.DATETIME, dbo.CONFIG.DESCRIPTION ) as rowid,
				dbo.DEVICE_SOCKET_DATES.IDSOCKET  AS SOCKET_ID,
				dbo.SOCKETS.IDCLIENT AS CLIENT_ID,
				dbo.READREGS.NOINS AS DEVICE_ID, 
				dbo.READREGS.UOM AS UOM,
				NULL AS CHANNEL,
				dbo.CONFIG.DESCRIPTION  AS VAR_DESCRIPTION, 
				NULL AS INTERVAL,
				NULL AS KE,
				dbo.READREGS.DATETIME AS DATETIME,
				dbo.READREGS.READVAL AS USAGE_DATA 
				FROM dbo.READREGS INNER JOIN
					dbo.CONFIG ON dbo.READREGS.NOINS = dbo.CONFIG.NOINS AND dbo.READREGS.IDVAR = dbo.CONFIG.IDVAR INNER JOIN
					dbo.DEVICE_SOCKET_DATES ON dbo.CONFIG.NOINS = dbo.DEVICE_SOCKET_DATES.NOINS INNER JOIN
					dbo.SOCKETS ON dbo.DEVICE_SOCKET_DATES.IDSOCKET = dbo.SOCKETS.IDSOCKET
				WHERE CONFIG.VARTYPE = 'R'
					AND READREGS.DATETIME BETWEEN @pStartDatetime AND @pEndDatetime
					AND READREGS.DATETIME >= DEVICE_SOCKET_DATES.START_DATE
					AND READREGS.DATETIME <= ISNULL(DEVICE_SOCKET_DATES.END_DATE,GETDATE())
					AND dbo.DEVICE_SOCKET_DATES.IDSOCKET = ISNULL(@pSocket, dbo.DEVICE_SOCKET_DATES.IDSOCKET)
					AND dbo.CONFIG.DESCRIPTION = ISNULL(@pIdvar, dbo.CONFIG.DESCRIPTION)
					-- Nuevas condiciones para nuevos parametros opcionales
					AND dbo.SOCKETS.IDCLIENT = ISNULL(@pIdClient, dbo.SOCKETS.IDCLIENT)
					AND dbo.READREGS.NOINS = ISNULL(@pIdDevice, dbo.READREGS.NOINS)
					AND dbo.READREGS.UOM = ISNULL(@pUOM, dbo.READREGS.UOM)								
					AND dbo.READREGS.READVAL = ISNULL(@UsageData, dbo.READREGS.READVAL)
				) as t1
			where t1.rowid between @pOffset and (@pOffset + @pTop)
			ORDER BY t1.DATETIME, t1.VAR_DESCRIPTION			
		END
		ELSE
		IF @pVartype = 'V'
		BEGIN
			select top (@pTop) t1.* from (
				SELECT 
				ROW_NUMBER() OVER ( ORDER BY dbo.QUALITY.DATETIME, dbo.CONFIG.DESCRIPTION ) as rowid,
				dbo.DEVICE_SOCKET_DATES.IDSOCKET  AS SOCKET_ID, 
				dbo.SOCKETS.IDCLIENT AS CLIENT_ID,
				dbo.QUALITY.NOINS AS DEVICE_ID, 
				NULL AS UOM,
				NULL AS CHANNEL,
				dbo.CONFIG.DESCRIPTION  AS VAR_DESCRIPTION, 
				NULL AS INTERVAL,
				NULL AS KE,
				DATEADD(MILLISECOND,MSEC,dbo.QUALITY.DATETIME) AS DATETIME,
				dbo.QUALITY.READVAL AS USAGE_DATA
				FROM dbo.QUALITY INNER JOIN
					dbo.CONFIG ON dbo.QUALITY.NOINS = dbo.CONFIG.NOINS AND dbo.QUALITY.IDVAR = dbo.CONFIG.IDVAR INNER JOIN
					dbo.DEVICE_SOCKET_DATES ON dbo.CONFIG.NOINS = dbo.DEVICE_SOCKET_DATES.NOINS INNER JOIN
					dbo.SOCKETS ON dbo.DEVICE_SOCKET_DATES.IDSOCKET = dbo.SOCKETS.IDSOCKET
				WHERE CONFIG.VARTYPE = 'V'
					AND QUALITY.DATETIME BETWEEN @pStartDatetime AND @pEndDatetime
					AND QUALITY.DATETIME >= DEVICE_SOCKET_DATES.START_DATE
					AND QUALITY.DATETIME <= ISNULL(DEVICE_SOCKET_DATES.END_DATE,GETDATE())
					AND dbo.DEVICE_SOCKET_DATES.IDSOCKET = ISNULL(@pSocket, dbo.DEVICE_SOCKET_DATES.IDSOCKET)
					AND dbo.CONFIG.DESCRIPTION = ISNULL(@pIdvar, dbo.CONFIG.DESCRIPTION)
					-- Nuevas condiciones para nuevos parametros opcionales
					AND dbo.SOCKETS.IDCLIENT = ISNULL(@pIdClient, dbo.SOCKETS.IDCLIENT)
					AND dbo.QUALITY.NOINS = ISNULL(@pIdDevice, dbo.QUALITY.NOINS)
					AND dbo.QUALITY.READVAL = ISNULL(@UsageData, dbo.QUALITY.READVAL)
				) as t1
			where t1.rowid between @pOffset and (@pOffset + @pTop)
			ORDER BY t1.DATETIME, t1.VAR_DESCRIPTION
		END

		COMMIT TRAN GetLPReadings
		SET @Cerror='Successful'
		RETURN 1
	END TRY
	BEGIN CATCH
		ROLLBACK TRAN GetLPReadings
		SELECT @Cerror =  ERROR_MESSAGE()
		RETURN  0
	END CATCH
END

